USE [UFDATA_001_2024]
GO
/****** Object:  StoredProcedure [dbo].[SaveStockOut4MtlApp]    Script Date: 2024-04-30 11:38:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*******说明*********
本存储过程可以根据单号，生产订单子件ID，创建人，仓库，数量等信息来创建一个只有一行明细的材料出库单，并进行审核
其中创建人、仓库、行ID以及数量等合法性，需要由调用者来保证正确。
真实部署时，应当修改cAcc_Id='001'为实际账套号。
本程序不支持以下要素
1.批号
2.序列号
3.自由项
4.多单位
5.出库跟踪入库
6.预留
本程序经过以下测试，但请务必在客户环境上重新进行下列测试
1.现存量报表测试
2.审批，取消审批，删单测试
3.存货核算记账测试
4.本程序与原生程序交叉制单测试（即测试3张单，分别从用本程序，原生程序来测试同一料号）
5.新开料号与旧料号分别测试
6.新开仓库与旧仓库分别测试
7.领料申请和行关闭的制单测试
8.对应生产订单非审批状态下测试
********************/

ALTER PROCEDURE [dbo].[SaveStockOut4MO]
@cCode1 int,@cMaker varchar(100),@cWhCode varchar(100),@AllocateId int,@iQuantity decimal(30,10),@msg varchar(500) output
AS
BEGIN

DECLARE @cCode VARCHAR(100)
SET @cCode = CONVERT(VARCHAR(100), @cCode1 + 1000000) 
SET @cCode = CONVERT(VARCHAR(6),GETDATE(),12) + @cCode  --加上年月日，比如240625 ，如果把VARCHAR(6)改成VARCHAR（4），则只取2406
SET @cCode = 'MD'+@cCode   --加上前缀
--此时@cCode应该是MD202425100012345 ,如果你的AUTOID = 12345的话

DECLARE @ACCID VARCHAR(100)
DECLARE @FATHERID VARCHAR(100)
DECLARE @CHILDID VARCHAR(100)
DECLARE @TODAY DATETIME
DECLARE @NOW DATETIME
DECLARE @COSTING BIT
DECLARE @cInvCode VARCHAR(100)
DECLARE @cDepCode VARCHAR(100)
DECLARE @iMQuantity decimal(30,10)
DECLARE @iNQuantity decimal(30,10)
DECLARE @cPsPcode VARCHAR(100)
DECLARE @cMoPode VARCHAR(100)
DECLARE @iMPoids INT
DECLARE @iMoSeq varchar(20)
DECLARE @iOpSeq varchar(20)
DECLARE @iPeSoDid INT
DECLARE @iPeSoType varchar(20)
DECLARE @iPeSoSeq INT
DECLARE @iSoType INT
DECLARE @cPeSoCode varchar(100)
DECLARE @MOID INT
DECLARE @APPSTATUS INT
SET @ACCID = '001' --真实环境应当修改这个
SELECT @TODAY = CONVERT(DATETIME, CONVERT(CHAR(20), GETDATE(), 110))
SELECT @NOW = GETDATE()
SELECT @COSTING=bInCost FROM Warehouse WHERE @cWhCode = cWhCode ;
IF EXISTS(SELECT 1 FROM RdRecord11 WHERE cCode = @cCode)
BEGIN
SET @MSG = '单号重复'
RETURN 1 
END

SELECT @cInvCode = B.InvCode,@cDepCode=A.MDeptCode , @cPsPcode = A.InvCode,
@cMoPode = C.MoCode ,@iNQuantity=B.Qty,@iMPoids=b.allocateid,@iMoSeq = a.SortSeq ,@iOpSeq=b.OpSeq,@iPeSoDid=b.allocateid,
@iPeSoType = '7',@cPeSoCode=C.MOCODE,@iPeSoSeq=a.sortseq,@iMQuantity = a.qty,@iSoType=0,@moid=c.moid,
@APPSTATUS=CASE 
	WHEN  ISNULL(IssQty,0)+@iQuantity > B.Qty THEN 1 --单据剩余可发量不足
	WHEN  Status != 3 THEN 2 --订单状态不是已审核
	END
FROM MOM_ORDERDETAIL A LEFT JOIN MOM_MOALLOCATE B ON A.MoDId = B.MoDId LEFT JOIN mom_order C ON C.MoId = A.MoId WHERE B.AllocateId = @AllocateId

IF (@cInvCode IS NULL)
BEGIN
SET @MSG = '不存在这个备料明细'
RETURN 2 
END 
IF (@APPSTATUS = 1)
BEGIN
SET @MSG = '单据剩余可发量不足'
RETURN 3 
END
IF (@APPSTATUS = 2)
BEGIN
SET @MSG = '订单状态不是已审核'
RETURN 4
END 
IF NOT EXISTS(SELECT 1 FROM Warehouse WHERE cWhCode = @cWhCode)
BEGIN
SET @MSG = '不存在该仓库'
RETURN 8 
END 

IF NOT EXISTS(SELECT 1 FROM  UFSystem.dbo.UA_Account WHERE cAcc_Id = @ACCID)
BEGIN
SET @MSG = 'ACCid配置错误'
RETURN 9 
END 

IF NOT EXISTS (SELECT 1 FROM CurrentStock WHERE cInvCode = @cInvCode AND cWhCode = @cWhCode AND iQuantity>=@iQuantity)
BEGIN
SET @MSG = '库存量不足以本次出库'
RETURN 10 
END
BEGIN TRANSACTION
--处理PK
SELECT @FATHERID=(1+iFatherId),@CHILDID=(1+iChildId) 
	FROM UFSystem.dbo.UA_Identity WITH(NOLOCK) WHERE cAcc_Id = @ACCID AND cVouchType = 'rd';
UPDATE UFSystem.dbo.UA_Identity SET iFatherId = @FATHERID,iChildId = @CHILDID WHERE cAcc_Id = @ACCID AND cVouchType = 'rd';
--写入材料出库单
INSERT INTO RdRecord11(ID,bRdFlag,cVouchType,cBusType,cSource,cWhCode,dDate,cCode,cMaker,cHandler,dVeriDate,VT_ID,dnmaketime,dnverifytime,cDepCode,iMQuantity,cMPoCode,cPsPcode,bOMFirst,bmotran,bHYVouch ,iproorderid  )
VALUES(@FATHERID+1000000000,0,'11','领料','生产订单',@cWhCode,@TODAY,@cCode,@cMaker,@cMaker,@TODAY,65,@NOW,@NOW,@cDepCode,@iMQuantity,@cMoPode,@cPsPcode,0,0,0,@moid);
IF @@ROWCOUNT != 1
BEGIN
SET @MSG = 'SQL执行INSERT INTO RdRecord11错误'
ROLLBACK TRANSACTION
RETURN 101
END
--写入出库单明细
INSERT INTO RdRecords11(AutoID,ID,cInvCode,iQuantity,irowno,bCosting,iNQuantity,iMPoIds,cmocode,invcode,imoseq,iopseq,ipesotype,cpesocode,ipesoseq,iExpiratDateCalcu,iorderdid,isotype,ipesodid) 
VALUES(@CHILDID+1000000000,@FATHERID+1000000000,@cInvCode,@iQuantity,1,@COSTING,@iNQuantity,@iMPoids,@cMoPode,@cPsPcode,@iMoSeq,@iOpSeq,@iPeSoType,@cPeSoCode,@iPeSoSeq,0,0,@isotype,@iPeSoDid);
IF @@ROWCOUNT != 1
BEGIN
SET @MSG = 'SQL执行INSERT INTO RdRecords11错误'
ROLLBACK TRANSACTION
RETURN 102
END
--更新仓库现存量，如果需要支持其他维度如批次维度等，需要在这里进行评估
UPDATE CurrentStock set iQuantity = iQuantity - @iQuantity WHERE cInvCode = @cInvCode AND cWhCode = @cWhCode
IF @@ROWCOUNT != 1
BEGIN
SET @MSG = 'SQL执行UPDATE CurrentStock错误' --如果某个仓库有多个批次或者自由项记录则会报这个错误
ROLLBACK TRANSACTION
RETURN 103
END
--回写对应的生产订单发料记录
UPDATE mom_moallocate SET IssQty = ISNULL(IssQty,0) + @iQuantity,requisitionissqty=ISNULL(requisitionissqty,0) + @iQuantity WHERE AllocateId = @iMPoids
IF @@ROWCOUNT != 1
BEGIN
SET @MSG = 'SQL执行UPDATE mom_moallocate错误' --如果某个仓库有多个批次或者自由项记录则会报这个错误
ROLLBACK TRANSACTION
RETURN 105
END
--更谨慎的做法是对每个UPDATE行的结果进行
SET @msg = @cCode
COMMIT TRANSACTION
END